package reports

import (
	"bytes"
	"fmt"
	"io/ioutil"
	"os"
	"strings"
	"time"

	"github.com/pkg/errors"
	"shqsoft.cn/pas3ui/base"
)

type dateRange struct {
	sdate string
	edate string
	days  int
}
type monthRange struct {
	smon string
	emon string
	days int
}

func parsesDateRange(sdate *time.Time, edate *time.Time) (dRanges []dateRange, mRange *monthRange, err error) {
	if sdate.Unix() > edate.Unix() {
		err = errors.Errorf("开始日期%s不能大于截至日期%s", sdate.Format("2006-01-02"), edate.Format("2006-01-02"))
		return
	}
	var sysDate *time.Time
	sysDate, _, err = base.GetSYSDate("ODS")
	finish := false
	dRanges = make([]dateRange, 0, 2)
	if sdate.Day() > 1 {
		nmf := time.Date(sdate.Year(), sdate.Month()+1, 1, 0, 0, 0, 0, time.UTC)
		edate2 := nmf.AddDate(0, 0, -1)
		if edate2.Unix() > edate.Unix() {
			edate2 = *edate
			finish = true
		}
		dr := dateRange{
			sdate: sdate.Format("2006-01-02"),
			edate: edate2.Format("2006-01-02"),
		}
		sdate = &nmf
		dRanges = append(dRanges, dr)
	}
	if !finish {
		if edate.AddDate(0, 0, 1).Day() > 1 && sysDate.Unix() != edate.Unix() {
			sdate2 := time.Date(edate.Year(), edate.Month(), 1, 0, 0, 0, 0, time.UTC)
			if sdate2.Unix() == sdate.Unix() {
				sdate2 = *sdate
				finish = true
			}
			dr := dateRange{
				sdate: sdate2.Format("2006-01-02"),
				edate: edate.Format("2006-01-02"),
			}
			pml := sdate2.AddDate(0, 0, -1)
			edate = &pml
			dRanges = append(dRanges, dr)
		}
	}
	if !finish {
		mRange = &monthRange{
			smon: sdate.Format("200601"),
			emon: edate.Format("200601"),
		}
	}
	return
}
func parseParamVals(repConf *Config, paramVals map[string]interface{}) (whereCyno string, sdatep, edatep *time.Time, err error) {
	nextData := false
	for _, p := range repConf.Params {
		if len(p.DataItems) > 0 && strings.Contains(p.DataItems[0].Label, "币") {
			cyno, ok := paramVals["cyno"].(string)
			if ok {
				if cyno == "1" {
					whereCyno = "1=1"
				} else if cyno == "2" {
					whereCyno = "CYNO='01'"
				} else if cyno == "3" {
					whereCyno = "CYNO<>'01'"
				} else {
					err = fmt.Errorf("生成报表%s数据出错，参数cyno的值%s无效，必须为1，2或者3", repConf.Name, cyno)
					return
				}
			}
		} else if p.DataType == "date" {
			date, ok := paramVals[p.Name].(time.Time)
			if !ok {
				err = fmt.Errorf("生成报表%s数据出错，参数%s不是有效的日期", repConf.Name, p.Name)
				return
			}
			if nextData {
				edatep = &date
			} else {
				sdatep = &date
				nextData = true
			}
		} else if len(p.DataItems) > 0 && strings.Contains(p.DataItems[0].Label, "良") {
			tp, ok := paramVals["tp"].(string)
			if ok {
				if tp == "1" {
					whereCyno = "TP=1"
				} else if tp == "0" {
					whereCyno = "TP=0"
				} else if tp == "2" {
					whereCyno = "1=1"
				} else {
					err = fmt.Errorf("生成报表%s数据出错，参数tp的值%s无效，必须为1，0或者2", repConf.Name, tp)
					return
				}
			}
		}
	}
	return
}
func CreateDataBySql(repConf *Config, tName string, paramVals map[string]interface{}) (err error) {
	if repConf.DataSource.SqlFileName == "" {
		return
	}
	var sqlStrs []string
	sqlStrs, err = parseSqlFile(repConf.DataSource.SqlFileName, paramVals)
	if err != nil {
		return
	}
	for _, sqlStr := range sqlStrs {
		if strings.TrimSpace(sqlStr) == "" {
			continue
		}
		err = base.CHDB.Exec(sqlStr).Error
		if err != nil {
			return
		}
	}
	return
}
func parseSqlFile(fileName string, paramVals map[string]interface{}) (sqlStrs []string, err error) {
	pvals := make(map[string]interface{}, len(paramVals))
	for k, v := range paramVals {
		dv, ok := v.(time.Time)
		if ok {
			pvals[k] = dv.Format("20060102")
		} else {
			pvals[k] = v
		}
	}
	var path string
	path, err = base.WorkPath()
	if err != nil {
		return
	}
	filePath := path + "/sql/" + fileName
	f, err := os.Open(filePath)
	if err != nil {
		return
	}
	var sqlBytes []byte
	sqlBytes, err = ioutil.ReadAll(f)
	if err != nil {
		return
	}
	sqlstr := string(sqlBytes)
	for k, v := range pvals {
		sqlstr = strings.ReplaceAll(sqlstr, "@"+k, fmt.Sprintf("%v", v))
	}
	sqlStrs = strings.Split(sqlstr, ";")
	return
}

//createDateRangeData 生成日期范围报表数据
func createDateRangeData(repConf *Config, tName string, paramVals map[string]interface{}) (err error) {
	if repConf.Type == "cross" {
		tName += "_list"
	}
	reptLock.Lock(tName)
	defer reptLock.Unlock(tName)
	var dbHelp *DataBase
	whereCyno, sdate, edate, err := parseParamVals(repConf, paramVals)
	if err != nil {
		return err
	}
	if sdate == nil {
		err = fmt.Errorf("生成报表%s数据出错，报表配置没有日期参数，不能使用createDateRangeData生成数据", repConf.Name)
		return
	}
	if edate == nil {
		err = fmt.Errorf("生成报表%s数据出错，报表配置没有截至日期，不能使用createDateRangeData生成数据", repConf.Name)
		return
	}
	var dRanges []dateRange
	var mRange *monthRange
	if repConf.DataSource.MFTable != "" {
		dRanges, mRange, err = parsesDateRange(sdate, edate)
		if err != nil {
			return
		}
	} else {
		dRanges = make([]dateRange, 1)
		dRanges[0].sdate = sdate.Format("2006-01-02")
		dRanges[0].edate = edate.Format("2006-01-02")
	}
	var exists bool
	dbHelp, err = NewDataBase(tName, repConf)
	if err != nil {
		return
	}
	exists, err = dbHelp.createTable("SummingMergeTree")
	if exists || err != nil {
		return
	}
	days := int(edate.Sub(*sdate).Hours())/24 + 1
	dropSql := fmt.Sprintf("drop table %s", tName)
	columns, err := repConf.GetLeafColumns()
	if err != nil {
		return
	}
	table := strings.ReplaceAll(repConf.DataSource.DFTable, "@edate", edate.Format("2006-01-02"))
	if table == "" {
		err = fmt.Errorf("生成报表%s数据出错，报表配置没有DFTable，不能使用createDateRangeData生成数据", repConf.Name)
	}
	where := "DTID=?"
	if whereCyno != "" {
		where += " AND " + whereCyno
	}
	if repConf.DataSource.Where != "" {
		ws, err := parseStringExp(repConf.DataSource.Where, paramVals, true)
		if err != nil {
			return err
		}
		where += " AND " + ws
	}
	if pw, ok := paramVals["#where"]; ok {
		where += " AND " + base.ToString(pw)
	}
	groupById := len(repConf.Groups)
	var ogbys []string
	if groupById > 0 {
		ogbys = repConf.Groups[groupById-1].By
	} else {
		ogbys = repConf.KeyColumns
	}
	gbys := make([]string, 0, len(ogbys)+len(repConf.ColumnLevels))
	cols := repConf.Columns
	if repConf.Type == "cross" {
		cols = repConf.Rows
	}
	for _, col := range cols {
		if base.InStringSlice(col.Name, ogbys) {
			colExp := col.ColExp
			if colExp == "" {
				colExp = col.Name
			} else if strings.HasPrefix(colExp, "@") {
				pName := colExp[1:]
				if pv, ok := paramVals[pName]; ok {
					colExp = base.ToString(pv)
				} else {
					err = fmt.Errorf("字段%s指定的参数colExp:%s无效", col.Name, colExp)
				}
			}
			gbys = append(gbys, colExp)
		}
	}
	for _, lev := range repConf.ColumnLevels {
		for _, col := range repConf.Columns {
			if lev.IdCol == col.Name && (col.ColExp == "" || col.ColExp == col.Name) {
				gbys = append(gbys, lev.IdCol)
				break
			}
		}
	}
	groupBy := strings.Join(gbys, ",")
	var insColExp, selColExp, sqlStr string
	insColExp, selColExp, err = parseSqlExp(columns, "acbl", days, paramVals)
	if err != nil {
		return
	}
	if repConf.DataSource.Having != "" {
		groupBy = fmt.Sprintf("%s HAVING %s", groupBy, repConf.DataSource.Having)
	}
	if len(insColExp) > 0 {
		sqlStr = fmt.Sprintf(`insert into %s(%s,gp_index) SELECT %s,? from %s where %s group by %s`, tName, insColExp, selColExp, table, where, groupBy)
		//时点
		_, err = dbHelp.db.Exec(sqlStr, groupById, edate.Format("2006-01-02"))
		if err != nil {
			dbHelp.db.Exec(dropSql)
			return
		}
	}
	insColExp, selColExp, err = parseSqlExp(columns, "sum", days, paramVals)
	if err != nil {
		return
	}
	if len(insColExp) > 0 {
		where = "DTID>=? AND DTID<=?"
		if whereCyno != "" {
			where += " AND " + whereCyno
		}
		if pw, ok := paramVals["#where"]; ok {
			where += " AND " + base.ToString(pw)
		}
		if repConf.DataSource.Where != "" {
			ws, err := parseStringExp(repConf.DataSource.Where, paramVals, true)
			if err != nil {
				return err
			}
			where += " AND " + ws
		}
		for _, dr := range dRanges {
			sqlStr = fmt.Sprintf(`insert into %s (%s,gp_index) SELECT %s,? from %s where %s group by %s`, tName, insColExp, selColExp, table, where, groupBy)
			_, err = dbHelp.db.Exec(sqlStr, groupById, dr.sdate, dr.edate)
			if err != nil {
				dbHelp.db.Exec(dropSql)
				return
			}
		}
		//日均(从每月物化视图中读取)
		if mRange != nil {
			where = "MONT>=? AND MONT<=?"
			if whereCyno != "" {
				where += " AND " + whereCyno
			}
			if pw, ok := paramVals["#where"]; ok {
				where += " AND " + base.ToString(pw)
			}
			if repConf.DataSource.Where != "" {
				ws, err := parseStringExp(repConf.DataSource.Where, paramVals, true)
				if err != nil {
					return err
				}
				where += " AND " + ws
			}
			table = strings.ReplaceAll(repConf.DataSource.MFTable, "@edate", edate.Format("2006-01-02"))
			insColExp, selColExp, err = parseSqlExp(columns, "sumMerge", days, paramVals)
			if err != nil {
				return
			}
			sqlStr = fmt.Sprintf(`insert into %s (%s,gp_index) SELECT %s,? from %s where %s group by %s`, tName, insColExp, selColExp, table, where, groupBy)
			_, err = dbHelp.db.Exec(sqlStr, groupById, mRange.smon, mRange.emon)
			if err != nil {
				dbHelp.db.Exec(dropSql)
				return err
			}
		}
	}
	err = dbHelp.createGroupBy(days)
	if err != nil {
		dbHelp.db.Exec(dropSql)
		return err
	}
	_, err = dbHelp.db.Exec(fmt.Sprintf("optimize table %s", tName))
	if err != nil {
		dbHelp.db.Exec(dropSql)
		return err
	}
	_, err = dbHelp.db.Exec(fmt.Sprintf("optimize table %s", tName))
	if err != nil {
		dbHelp.db.Exec(dropSql)
		return err
	}
	_, err = dbHelp.db.Exec(fmt.Sprintf("optimize table %s", tName))
	if err != nil {
		dbHelp.db.Exec(dropSql)
		return err
	}
	return
}

func parseSqlExp(cols []*Column, sqlType string, days int, paramVals map[string]interface{}) (insColExp string, selColExp string, err error) {
	isMerge := false
	hasSum := false
	hasAcbl := false
	var gCalc string
	for i := 0; i < len(cols); i++ {
		gCalc = cols[i].GroupCalc
		if gCalc == "acbl" {
			hasAcbl = true
		} else if gCalc == "sum" || gCalc == "avg" || strings.Contains(cols[i].ColExp, "sum") {
			hasSum = true
		}
	}
	if sqlType == "acbl" {
		hasSum = false
		if !hasAcbl {
			return
		}
	}
	if strings.HasPrefix(sqlType, "sum") {
		hasAcbl = false
		if !hasSum {
			return
		}
	}
	if sqlType == "sumMerge" {
		isMerge = true
	}
	insertFieldsArr := make([]string, 0, 30)
	selectFieldsArr := make([]string, 0, 30)
	var colExp string
	edate, _ := paramVals["edate"].(time.Time)
	for _, col := range cols {
		gCalc = col.GroupCalc
		colExp = strings.ReplaceAll(col.ColExp, "@edate", edate.Format("2006-01-02"))
		if colExp == "" {
			colExp = col.Name
		} else if strings.HasPrefix(colExp, "@") {
			pName := colExp[1:]
			if pv, ok := paramVals[pName]; ok {
				colExp = base.ToString(pv)
			} else {
				err = fmt.Errorf("字段%s指定的参数colExp:%s无效", col.Name, colExp)
			}
		}
		if gCalc == "" || gCalc == "none" {
			insertFieldsArr = append(insertFieldsArr, col.Name)
			if isMerge {
				colExp = strings.ReplaceAll(colExp, "sum", "sumMerge")
			}
			selectFieldsArr = append(selectFieldsArr, colExp+" AS "+col.Name)
		} else if gCalc == "acbl" {
			if hasAcbl {
				insertFieldsArr = append(insertFieldsArr, col.Name)
				if col.Where == "" {
					selectFieldsArr = append(selectFieldsArr, fmt.Sprintf("sum(%s) AS %s", colExp, col.Name))
				} else {
					selectFieldsArr = append(selectFieldsArr, fmt.Sprintf("sumIf(%s,%s) AS %s", colExp, col.Where, col.Name))
				}
			}
		} else if gCalc == "anyLast" {
			insertFieldsArr = append(insertFieldsArr, col.Name)
			if isMerge {
				selectFieldsArr = append(selectFieldsArr, fmt.Sprintf("anyLastMerge(%s) AS %s", colExp, col.Name))
			} else {
				selectFieldsArr = append(selectFieldsArr, fmt.Sprintf("anyLast(%s) AS %s", colExp, col.Name))
			}
		} else if gCalc == "avg" || gCalc == "sum" {
			if !hasSum {
				continue
			}
			if !strings.Contains(colExp, "sum") {
				colExp, err = colExpToSum(colExp, isMerge, col.Where)
				if err != nil {
					return
				}
			} else {
				if isMerge {
					colExp = strings.ReplaceAll(colExp, "sum", "sumMerge")
				}
			}
			insertFieldsArr = append(insertFieldsArr, col.Name)
			if gCalc == "avg" {
				selectFieldsArr = append(selectFieldsArr, fmt.Sprintf("(%s)/%d AS %s", colExp, days, col.Name))
			} else {
				selectFieldsArr = append(selectFieldsArr, colExp+" AS "+col.Name)
			}
		} else {
			err = fmt.Errorf("字段%s指定的groupCalc:%s无效", col.Name, gCalc)
		}
	}
	insColExp = strings.Join(insertFieldsArr, ",")
	selColExp = strings.Join(selectFieldsArr, ",")
	return
}
func colExpToSum(colExp string, isMerge bool, where string) (smExp string, err error) {
	exps := make([]string, 0, 10)
	expBytes := make([]byte, 0, 16)
	sm := map[int]string{1: "+,-", 2: "*,/", 3: "非运算符"} //状态标识
	sc := map[int][]int{0: {1, 3}, 1: {3}, 3: {1, 2, 3}, 2: {3}}
	stat := 0
	st := sc[0]
	var isOk bool
	for i := 0; i < len(colExp); i++ {
		b := colExp[i]
		for _, s := range st {
			if s == 1 && (b == '+' || b == '-') {
				stat = 1
				st = sc[1]
				isOk = true
				break
			} else if s == 2 && (b == '*' || b == '/') {
				stat = 2
				st = sc[2]
				isOk = true
				break
			} else if s == 3 && (b != '+' && b != '-' && b != '*' && b != '/') {
				stat = 3
				st = sc[3]
				isOk = true
				break
			}
		}
		if !isOk {
			msg := ""
			for i, s := range st {
				msg += sm[s]
				if i < len(st)-1 {
					msg += "或者"
				}
			}
			err = fmt.Errorf("错误的表达式%s，表达式的第%d位必须是%s", colExp, i, msg)
			return
		}
		if stat == 1 || stat == 2 {
			if len(expBytes) > 0 {
				exps = append(exps, string(expBytes))
				expBytes = make([]byte, 0, 16)
			}
			exps = append(exps, string(b))
		} else {
			expBytes = append(expBytes, b)
		}
	}
	if len(expBytes) == 0 {
		err = errors.New("表达式的最后不能以运算符结尾")
		return
	}
	exps = append(exps, string(expBytes))
	var buff bytes.Buffer
	sExp := "sum"
	if isMerge {
		sExp = "sumMerge"
	}
	if where != "" {
		sExp += "If"
	}
	for _, exp := range exps {
		if exp == "+" || exp == "-" || exp == "*" || exp == "/" {
			buff.WriteString(exp)
			continue
		}
		buff.WriteString(sExp)
		buff.WriteString("(")
		buff.WriteString(exp)
		if where != "" {
			buff.WriteString(",")
			buff.WriteString(where)
		}
		buff.WriteString(")")
	}
	smExp = buff.String()
	return
}
func createCalcs(repConf *Config, tName string, paramVals map[string]interface{}, calcs map[string][]string) (err error) {
	if len(repConf.CalcCols) == 0 {
		return
	}
	whereCyno, sdate, edate, err := parseParamVals(repConf, paramVals)
	if err != nil {
		return err
	}
	if sdate == nil {
		err = fmt.Errorf("生成报表%s数据出错，报表配置没有日期参数，不能添加计算", repConf.Name)
		return
	}
	var dbHelp *DataBase
	dbHelp, err = NewDataBase(tName, repConf)
	if err != nil {
		return
	}
	cols, err := repConf.GetLeafColumns()
	if err != nil {
		return
	}
	var col *Column
	for k, v := range calcs {
		col = nil
		for _, c := range cols {
			if c.Name == k {
				col = c
			}
		}
		if col == nil {
			err = fmt.Errorf("生成报表%s数据出错，列名%s在报表中没有找到", repConf.Name, k)
			return
		}
		for _, calcName := range v {
			err := dbHelp.createColumn(col, calcName, sdate, edate, whereCyno, paramVals)
			if err != nil {
				dropSql := fmt.Sprintf("drop table %s", tName)
				dbHelp.db.Exec(dropSql)
				return err
			}
		}
	}
	dbHelp.db.Exec(fmt.Sprintf("optimize table %s", tName))
	dbHelp.db.Exec(fmt.Sprintf("optimize table %s", tName))
	dbHelp.db.Exec(fmt.Sprintf("optimize table %s", tName))
	return
}

func parseStringExp(name string, paramVals map[string]interface{}, isSql bool) (string, error) {
	if paramVals == nil {
		return "", fmt.Errorf("解析表达式%s的出错，参数没有初始化！", name)
	}
	format := "20060102"
	if isSql {
		format = "2006-01-02"
	}
	var pName string
	for k, v := range paramVals {
		pName = "@" + k
		var val string
		if dVal, ok := v.(time.Time); ok {
			val = dVal.Format(format)
		} else {
			val = base.ToString(v)
		}
		name = strings.Replace(name, pName, val, -1)
	}
	if strings.Contains(name, "@") || strings.Contains(name, "#") {
		return "", fmt.Errorf("解析表达式%s的错误，没有完全解析，解析之后是%s！", name, name)
	}
	return name, nil
}
